#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlite3.h>

int main(void) {
    sqlite3 *db;
    char *err_msg = 0;
    
    // Open database
    int rc = sqlite3_open("files.db", &db);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        return 1;
    }

    // Create table
    const char *sql = "CREATE TABLE IF NOT EXISTS files ("
                     "id INTEGER PRIMARY KEY AUTOINCREMENT,"
                     "name TEXT NOT NULL,"
                     "data BLOB"
                     ");";

    rc = sqlite3_exec(db, sql, 0, 0, &err_msg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL error: %s\n", err_msg);
        sqlite3_free(err_msg);
        sqlite3_close(db);
        return 1;
    }

    // Read binary file
    FILE *fp = fopen("test.jpg", "rb");
    if (!fp) {
        fprintf(stderr, "Cannot open file\n");
        sqlite3_close(db);
        return 1;
    }

    // Get file size
    fseek(fp, 0, SEEK_END);
    long file_size = ftell(fp);
    fseek(fp, 0, SEEK_SET);

    // Read file into buffer
    char *buffer = malloc(file_size);
    if (!buffer) {
        fprintf(stderr, "Memory allocation failed\n");
        fclose(fp);
        sqlite3_close(db);
        return 1;
    }
    fread(buffer, file_size, 1, fp);
    fclose(fp);

    // Prepare insert statement
    sqlite3_stmt *stmt;
    const char *insert_sql = "INSERT INTO files (name, data) VALUES (?, ?);";
    rc = sqlite3_prepare_v2(db, insert_sql, -1, &stmt, 0);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Failed to prepare statement: %s\n", sqlite3_errmsg(db));
        free(buffer);
        sqlite3_close(db);
        return 1;
    }

    // Bind parameters
    sqlite3_bind_text(stmt, 1, "test.jpg", -1, SQLITE_STATIC);
    sqlite3_bind_blob(stmt, 2, buffer, file_size, SQLITE_STATIC);

    // Execute statement
    rc = sqlite3_step(stmt);
    if (rc != SQLITE_DONE) {
        fprintf(stderr, "Execution failed: %s\n", sqlite3_errmsg(db));
    } else {
        printf("File stored successfully!\n");
    }

    // Read blob back
    sqlite3_stmt *read_stmt;
    const char *select_sql = "SELECT data FROM files WHERE name = ? LIMIT 1;";
    rc = sqlite3_prepare_v2(db, select_sql, -1, &read_stmt, 0);
    sqlite3_bind_text(read_stmt, 1, "test.jpg", -1, SQLITE_STATIC);

    if (sqlite3_step(read_stmt) == SQLITE_ROW) {
        const void *blob_data = sqlite3_column_blob(read_stmt, 0);
        int blob_size = sqlite3_column_bytes(read_stmt, 0);

        // Save blob to new file
        FILE *out = fopen("output.jpg", "wb");
        if (out) {
            fwrite(blob_data, blob_size, 1, out);
            fclose(out);
            printf("File retrieved and saved as output.jpg\n");
        }
    }

    // Cleanup
    sqlite3_finalize(stmt);
    sqlite3_finalize(read_stmt);
    free(buffer);
    sqlite3_close(db);

    return 0;
}